# @Time :  
# @Author : Fioman 
# @Phone : 13149920693
"""
数据库访问操作类,主要用来访问数据库,比如创建,增加,查询,修改,更新等
"""
from eap_mysql.mysql_logger import MysqlLogger
from eap_mysql.mysql_operator import MysqlOperator

sql_logger = MysqlLogger()


def create_table_pc_data_craft():
    """
    创建工艺质量表
    @return:
    """
    with MysqlOperator() as sp:
        sql = "Create Table If Not Exists PC_DATA_CRAFT" + "(" + \
              "ID BIGINT PRIMARY KEY AUTO_INCREMENT," + \
              "CONTAINER VARCHAR(50)," + \
              "PNLIDORSETID VARCHAR(50)," + \
              "ITEMCODE VARCHAR(30)," + \
              "ITEMDES VARCHAR(100)," + \
              "UOM VARCHAR(30)," + \
              "ITEMVALUE VARCHAR(200)," + \
              "ISOK VARCHAR(1)," + \
              "GROUPNUM BIGINT," + \
              "CREATETIME DATETIME" + \
              ")"

        sp.create_table(sql)


def create_table_pc_data_manualmaterial():
    """
    创建手动参数调整表
    @return:
    """
    with MysqlOperator() as sp:
        sql = "Create Table If Not Exists PC_DATA_MANUALMATERIAL" + "(" + \
              "ID BIGINT PRIMARY KEY AUTO_INCREMENT," + \
              "CONTAINER VARCHAR(50)," + \
              "ITEMCODE VARCHAR(30)," + \
              "ITEMDES VARCHAR(100)," + \
              "UOM VARCHAR(30)," + \
              "OLDITEMVALUE VARCHAR(200)," + \
              "NEWITEMVALUE VARCHAR(200)," + \
              "ISOK VARCHAR(1)," + \
              "CREATETIME DATETIME" + \
              ")"

        sp.create_table(sql)


def create_table_pc_data_emp():
    """
    员工上下机记录上传表
    @return:
    """
    with MysqlOperator() as sp:
        sql = "Create Table If Not Exists PC_DATA_EMP" + "(" + \
              "ID BIGINT PRIMARY KEY AUTO_INCREMENT," + \
              "EMPNO VARCHAR(50)," + \
              "EMPNAME VARCHAR(50)," + \
              "EMPCODE VARCHAR(50)," + \
              "EMPDES VARCHAR(50)," + \
              "EMPTIME DATETIME," + \
              "CREATETIME DATETIME" + \
              ")"

        sp.create_table(sql)


def create_table_pc_data_alarm():
    """
    创建报警表
    @return:
    """
    with MysqlOperator() as sp:
        sql = "Create Table If Not Exists PC_DATA_ALARM" + "(" + \
              "ID BIGINT PRIMARY KEY AUTO_INCREMENT," + \
              "ALARMCODE VARCHAR(30)," + \
              "ALARMDES VARCHAR(200)," + \
              "ALARMVALUE VARCHAR(1)," + \
              "CREATETIME DATETIME" + \
              ")"
        sp.create_table(sql)


def create_table_pc_data_status():
    """
    创建不实时采集设备状态表
    @return:
    """
    with MysqlOperator() as sp:
        sql = "Create Table If Not Exists PC_DATA_STATUS" + "(" + \
              "ID BIGINT PRIMARY KEY AUTO_INCREMENT," + \
              "STATUSCODE VARCHAR(30)," + \
              "STATUSDES VARCHAR(200)," + \
              "STATUSVALUE VARCHAR(50)," + \
              "CREATETIME DATETIME" + \
              ")"
        sp.create_table(sql)


def create_table_pc_data_count():
    """
    创建当前作业工单统计的数量表
    @return:
    """
    with MysqlOperator() as sp:
        sql = "Create Table If Not Exists PC_DATA_COUNT" + "(" + \
              "ID BIGINT PRIMARY KEY AUTO_INCREMENT," + \
              "COUNT INT," + \
              "UPDATETIME DATETIME" + \
              ")"
        sp.create_table(sql)


def create_table_pc_signal_status():
    """
    创建设备实时信号表
    @return:
    """
    with MysqlOperator() as sp:
        sql = "Create Table If Not Exists PC_SIGNAL_STATUS" + "(" + \
              "ID BIGINT PRIMARY KEY AUTO_INCREMENT," + \
              "SIGNALCODE VARCHAR(30)," + \
              "SIGNALDES VARCHAR(200)," + \
              "SIGNALVALUE VARCHAR(50)," + \
              "CREATETIME DATETIME" + \
              ")"
        sp.create_table(sql)


def create_table_eap_signal_status():
    """
    创建Eap实时信号表
    @return:
    """
    with MysqlOperator() as sp:
        sql = "Create Table If Not Exists EAP_SIGNAL_STATUS" + "(" + \
              "ID BIGINT PRIMARY KEY AUTO_INCREMENT," + \
              "SIGNALCODE VARCHAR(30)," + \
              "SIGNALDES VARCHAR(200)," + \
              "SIGNALVALUE VARCHAR(50)," + \
              "CREATETIME DATETIME" + \
              ")"
        sp.create_table(sql)


def create_table_eap_material_data():
    """
    创建EAP资料下发表,该表为EAP下发资料表,主要为了EAP下发资料到设备.当设备监控到EAP下发资料到设备完成信号时,设备读取
    该表资料信息,然后自动加载到设备资料,这个表平时不需要轮询的,只有在接收到信号之后才需要去查看
    @return:
    """
    with MysqlOperator() as sp:
        sql = "Create Table If Not Exists EAP_MATERIAL_DATA" + "(" + \
              "ID BIGINT PRIMARY KEY AUTO_INCREMENT," + \
              "PRODUCT VARCHAR(50)," + \
              "CONTAINER VARCHAR(50)," + \
              "COUNT VARCHAR(50)," + \
              "MATERIALCODE VARCHAR(10)," + \
              "ITEMCODE VARCHAR(30)," + \
              "ITEMDES VARCHAR(100)," + \
              "UOM VARCHAR(30)," + \
              "ITEMVALUE VARCHAR(500)," + \
              "GROUPNUM BIGINT," + \
              "CREATETIME DATETIME" + \
              ")"
        sp.create_table(sql)


# 访问表数据,获取 pc_data_craft 表数据
def get_pc_data_craft_data():
    """
    获取 pc_data_craft 表数据
    """
    try:
        table_name = "PC_DATA_CRAFT"
        with MysqlOperator() as sp:
            return sp.fetch_all(table_name)
    except Exception as e:
        sql_logger.debug("in get_pc_data_craft_data() error:  {}".format(str(e)))
        return []


def get_pc_data_manual_data():
    """
    获取PC_DATA_MANUALMATERIAL 表数据
    @return:
    """
    try:
        table_name = "PC_DATA_MANUALMATERIAL"
        with MysqlOperator() as sp:
            return sp.fetch_all(table_name)
    except Exception as e:
        sql_logger.debug("in get_pc_data_manual_data() error:  {}".format(str(e)))
        return []


def get_pc_data_alarm_data():
    """
    获取 PC_DATA_ALARM 表数据
    """
    try:
        table_name = "PC_DATA_ALARM"
        with MysqlOperator() as sp:
            return sp.fetch_all(table_name)
    except Exception as e:
        sql_logger.debug("in get_pc_data_alarm_data() error: {}".format(str(e)))


def get_pc_data_status_data():
    """
    获取 PC_DATA_STATUS 表数据
    """
    try:
        table_name = "PC_DATA_STATUS"
        with MysqlOperator() as sp:
            return sp.fetch_all(table_name)
    except Exception as e:
        sql_logger.debug("in get_pc_data_status_data() error: {}".format(str(e)))


def get_pc_data_count_data():
    """
    获取 PC_DATA_COUNT 表数据
    """
    try:
        table_name = "PC_DATA_COUNT"
        with MysqlOperator() as sp:
            return sp.fetch_all(table_name)
    except Exception as e:
        sql_logger.debug("in get_pc_data_count_data() error: {}".format(str(e)))


def get_pc_signal_status_data():
    """
    获取 PC_SIGNAL_STATUS 表数据
    """
    try:
        table_name = "PC_SIGNAL_STATUS"
        with MysqlOperator() as sp:
            return sp.fetch_all(table_name)
    except Exception as e:
        sql_logger.debug("in get_pc_signal_status_data() error: {}".format(str(e)))


def get_eap_signal_status_data():
    """
    获取 EAP_SIGNAL_STATUS 表数据
    """
    try:
        table_name = "EAP_SIGNAL_STATUS"
        with MysqlOperator() as sp:
            return sp.fetch_all(table_name)
    except Exception as e:
        sql_logger.debug("in get_eap_signal_status_data() error: {}".format(str(e)))
        return []


def get_eap_material_data_data():
    """
    获取 EAP_MATERIAL_DATA 表数据
    """
    try:
        table_name = "EAP_MATERIAL_DATA"
        with MysqlOperator() as sp:
            return sp.fetch_all(table_name)
    except Exception as e:
        sql_logger.debug("in get_eap_signal_status_data() error: {}".format(str(e)))
        return []


def get_pc_data_emp_data():
    """
      获取 pc_data_emp 表数据
      """
    try:
        table_name = "PC_DATA_EMP"
        with MysqlOperator() as sp:
            return sp.fetch_all(table_name)
    except Exception as e:
        sql_logger.debug("in get_pc_data_emp() error: {}".format(str(e)))
        return []


def delete_meterial_data():
    """
    删除 EAP_MATERIAL_DATA 表数据
    @return:
    """
    try:
        tableName = "EAP_MATERIAL_DATA"
        with MysqlOperator() as sp:
            return sp.delete_table_data(tableName)
    except Exception as e:
        sql_logger.debug("in delete_meterial_data() error: {}".format(str(e)))
        return []


def delete_eap_signal_record_by_field(fieldName, signalCode):
    """
    删除eap_signal_status 表中的记录
    @param fieldName:
    @param signalCode:
    @return:
    """
    tableName = "EAP_SIGNAL_STATUS"
    with MysqlOperator() as sp:
        sp.delete_record_by_field_val(tableName, fieldName, signalCode)


if __name__ == '__main__':
    res = get_pc_data_craft_data()
    print("res = {}".format(res))
